/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Servlets;

import static Servlets.InsertarAction.con;
import static Servlets.InsertarAction.connectDB;
import static Servlets.InsertarAction.insertData;
import static Servlets.InsertarAction.loadHSQLDB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

/**
 *
 * @author RICARDO
 */
public class Insertar1Action extends org.apache.struts.action.Action {

    /* forward name="success" path="" */
    private static final String SUCCESS = "success";

    static Connection con  = null;
    static Statement  st   = null;  
    static ResultSet  res  = null; 
    static String     sql  = null;
    private static final String DBH = "jdbc:hsqldb:mem:memoriadb";
            boolean status;
            int i;
    /**
     * This is the action called from the Struts framework.
     *
     * @param mapping The ActionMapping used to select this instance.
     * @param form The optional ActionForm bean for this request.
     * @param request The HTTP Request we are processing.
     * @param response The HTTP Response we are processing.
     * @throws java.lang.Exception
     * @return
     */
    @Override
    public ActionForward execute(ActionMapping mapping, ActionForm form,
            HttpServletRequest request, HttpServletResponse response)
            throws Exception {
                  //crearemos una BD en memoria

        loadHSQLDB();
        connectDB();
        createStatement();
        //dropTables();
        //createTables();
        //restoreDB();
        //menu();
        insertData();
        //closeHSQLDB();
                
        return mapping.findForward("success");
    }
    
    public static void loadHSQLDB()
    {
        System.out.println("* Starting...");
        try
        {
            Class.forName("org.hsqldb.jdbcDriver"); //Load HSQLDB driver
            System.out.println("* Loading HSQLDB driver...");
        } 
        catch(Exception e)
        {  
            System.err.println("ERROR: failed to load HSQLDB JDBC driver.");
            e.printStackTrace();
            return;  
        } 
    }
            public static void connectDB()
    {
        try
        {   // Connect to the database or create if it don't exist 
            con = DriverManager.getConnection(DBH); 
            System.out.println("* Creating HSQLDB connection...");
        }
        catch(Exception e)
        {
            System.err.println("Error: " + e.getMessage());
            return;
        }
    }
          public static void createStatement()
    {
        try
        {
            st  = con.createStatement(); 
        }
        catch (Exception e)
        {  
            System.err.println("Error: createStatement: " + e.getMessage());   
            return;
        }
    }  
        
        
          
    public static void dropTables()
    {
        try 
        {   // Drop tables
            st.executeUpdate("DROP TABLE IF EXISTS stusub;DROP TABLE IF EXISTS cliente;DROP TABLE IF EXISTS subject;");
            System.out.println("* Drop table if exists...");
        }
        catch (Exception e)
        {  
            System.err.println("Warning: drop table: " + e.getMessage());   
        }  
    }
 
    public static void createTables()
    {
        try 
        {   // Create tables
            sql = "CREATE TABLE cliente ( " +
                                            "id         INTEGER NOT NULL PRIMARY KEY, " +
                                            "nombre       VARCHAR(25) NOT NULL, " +
                                            "apellido_1   VARCHAR(100), " +
                                            "direccion    VARCHAR(100), " +
                                            "ciudad       VARCHAR(100), " +
                                            "codigopostal      VARCHAR(5), " +
                                            "email      VARCHAR(80), " +
                                            "telefono     VARCHAR(50), " +
                                            "subject VARCHAR(40)," +
                                            "CONSTRAINT UNIQUE_CLI UNIQUE(nombre)" +
                                         ");";  
            st.executeUpdate(sql);  
            System.out.println("* Creada tabla cliente ...");
 
            sql = "CREATE TABLE subject ( " +
                                            "id         INTEGER NOT NULL PRIMARY KEY, " +
                                            "name       VARCHAR(100) NOT NULL, " +
                                            "CONSTRAINT UNIQUE_SUB UNIQUE(name)" +
                                         ");";  
            st.executeUpdate(sql);  
            System.out.println("* Creada tabla subject ...");
 
            sql = "CREATE TABLE stusub ( " +
                                            "id             INTEGER NOT NULL PRIMARY KEY, " +
                                            "subject        INTEGER NOT NULL, " +
                                            "student       INTEGER NOT NULL, " +
                                            "FOREIGN KEY (student) REFERENCES cliente(id), " +
                                            "FOREIGN KEY (subject) REFERENCES subject(id), " +
                                            "CONSTRAINT UNIQUE_STUSUB UNIQUE(subject, student)" +
                                         ");";  
            st.executeUpdate(sql);  
            
            System.out.println("* Creada tabla cliente-subject ...");
        } 
        catch (Exception e)
        {  
            System.err.println("Warning: CREATE TABLE: " + e.getMessage()); 
           
        }
    }
 
    public static void restoreDB()
    {
        try 
        {   // Restore the database  
            st.executeUpdate("DELETE FROM stusub;DELETE FROM student;DELETE FROM subject;");
            System.out.println("* Restore the original databases, removing all previous inserting data.");
        }
        catch (Exception e)
        {  
            System.err.println("Warning: error on vacuum tables: " + e.getMessage());   
        } 
    }
    
        public static void insertData()
    {
        // Insert data  
        System.out.println("Insertando datos en tablas");
        try 
        { 
            //Insert subject
            st.executeUpdate("INSERT INTO subject VALUES ('3', 'Matematicas')");  
  
 
            //Insert student
            st.executeUpdate("INSERT INTO cliente VALUES ('3', 'Ricardit', 'Asensi', 'Sol, 12', 'Sevilla', '13207', 'j.garrido@gmail.com', '609555321', '0')");  
              
 
            //Foreign keys stusub
           st.executeUpdate("INSERT INTO stusub VALUES ('3', '0', '2')");  
 
            
        }
        catch (Exception e)
        {  
            System.err.println("Warning: error on insert data: " + e.getMessage());   
            e.printStackTrace();
            return; 
        } 
    }
    
    public static void closeHSQLDB()
    {
        // Save temporal data and close
        try
        {
            st = con.createStatement();  
            st.executeUpdate("SHUTDOWN");  
            st.close(); 
        }
        catch(Exception e)
        {
            System.out.println("Error: save temporal data" + e.getMessage());
        }
 
        try
        {
            con.close();
        }
        catch(Exception e)
        {
            System.out.println("Error: " + e.getMessage());
            e.printStackTrace();
            return; 
        }
    }
}
